package com.liuh.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.db.handler.BeanListHandler;
import cn.hutool.db.sql.NamedSql;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.setting.dialect.Props;

/**
 * @note 数据库连接与关闭工具类
 * @author liuh
 * @date 2021年4月29日 上午11:06:48
 */
public class BaseDao {

	protected Connection conn;
	static Props props;

	static {
		try {
			props = new Props("database.properties");
			Class.forName(props.getStr("driver"));
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 获取数据库连接对象。
	 */
	public Connection getConnection() {
		// 获取连接并捕获异常
		try {
			if (conn == null || conn.isClosed())
				conn = DriverManager.getConnection(props.getStr("url"), props.getStr("user"), props.getStr("password"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;// 返回连接对象
	}

	/**
	 * 关闭数据库连接。
	 * 
	 * @param conn
	 *            数据库连接
	 * @param stmt
	 *            Statement对象
	 * @param rs
	 *            结果集
	 */
	public void closeAll(Connection conn, Statement stmt, ResultSet rs) {
		// 若结果集对象不为空，则关闭
		try {
			if (rs != null && !rs.isClosed())
				rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 若Statement对象不为空，则关闭
		try {
			if (stmt != null && !stmt.isClosed())
				stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 若数据库连接对象不为空，则关闭
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 增、删、改操作
	 * 
	 * @param sql
	 *            sql语句
	 * @param prams
	 *            参数数组
	 * @return 执行结果
	 */
	protected int executeUpdate(String sql, Object... params) {
		int result = 0;
		conn = this.getConnection();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject(i + 1, params[i]);
			}
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, pstmt, null);
		}
		return result;
	}
	
	protected int executeUpdate(String sql, Object bean) {
		int result = 0;
		conn = this.getConnection();
		Map<String, Object> paramMap = BeanUtil.beanToMap(bean);
		NamedSql namedSql = new NamedSql(sql, paramMap);
		try {
			result = SqlExecutor.execute(conn, namedSql.getSql(), namedSql.getParams());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(conn, null, null);
		}
		return result;
	}

	/**
	 * 查询操作
	 * 
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数数组
	 * @return 查询结果集
	 */
	protected List<Map<String, Object>> executeQuery(String sql, Object... params) {
		List<Map<String, Object>> list = CollUtil.newArrayList();
		conn = this.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject(i + 1, params[i]);
			}
			rs = pstmt.executeQuery();
			// resultSet to List<Map<String,object>>
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new TreeMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					map.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	/**
	 * @note hutool SqlExecutor.query 注意：Bean属性命名符合驼峰命名规则
	 * @author liuh
	 * @date 2021年5月7日 上午11:43:39
	 */
	protected <E> List<E> executeQuery(String sql, Class<E> beanType, Object... params) {
		List<E> list = CollUtil.newArrayList();
		conn = this.getConnection();
		try {
			list = SqlExecutor.query(conn, sql, BeanListHandler.create(beanType), params);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {
			closeAll(conn, null, null);
		}
		return list;
	}

}
